package jmu.db.test.xtest.util ;


import java.sql.Connection ;
import java.sql.PreparedStatement ;
import java.sql.ResultSet ;
import java.sql.SQLException ;
import java.util.LinkedList ;
import java.util.List ;
import java.util.Random ;

import jmu.db.test.xtest.TaskResult ;
import jmu.db.test.xtest.TestField ;
import jmu.db.test.xtest.TestTable ;
import jmu.db.test.xtest.TestTask ;
import jmu.db.test.xtest.common.Identity ;

public class TaskHelperImpl extends TaskHelper
{
    private static String SQL_CREATE_RESULT = "INSERT INTO X_TEST_TASKS VALUES (?,?,'STARTED',SYSDATE,null,null)" ;
    private static String SQL_UPDATE_RESULT = "UPDATE X_TEST_TASKS SET end_date = SYSDATE, total = ?, pass = ? WHERE id = ?" ;

    private static String SQL_GET_TASK = "SELECT * FROM X_TEST_TASK_DEF WHERE task_id = ?" ;
    private static String SQL_LIST_TASK = "SELECT * FROM X_TEST_TASK_DEF" ;

    private static String SQL_LIST_TABLE = "SELECT d.* FROM X_TEST_TABLE_DEF d, X_TEST_TABLES ref "
            + "WHERE d.table_id = ref.table_id and ref.task_id = ?" ;

    private static String SQL_LIST_FIELD = "SELECT d.* FROM X_TEST_FIELD_DEF d, X_TEST_FIELDS ref "
            + "WHERE d.field_id = ref.field_id and ref.table_id = ?" ;

    public TaskHelperImpl ( final Connection connection ) throws SQLException
    {
        super ( connection ) ;
    }

    @Override
    public int createResult ( final Identity taskid ) throws SQLException
    {
        // TODO: generate id from database
        final int resultid = new Random ().nextInt ( 1500 ) ;

        // get prepared statement
        final PreparedStatement ps = getPreparedStatement ( SQL_CREATE_RESULT ) ;

        // set parameters
        ps.setInt ( 1, resultid ) ;
        ps.setInt ( 2, taskid.getId () ) ;

        // execute
        ps.execute () ;

        // clear parameters
        ps.clearParameters () ;

        return resultid ;
    }

    @Override
    public List < TestField > listFields ( final Identity tableid ) throws SQLException
    {
        // get prepared statement
        final PreparedStatement ps = getPreparedStatement ( SQL_LIST_FIELD ) ;

        // set parameters
        ps.setInt ( 1, tableid.getId () ) ;

        // execute
        final ResultSet rs = ps.executeQuery () ;

        // read fields
        final List < TestField > list = new LinkedList < TestField > () ;
        while ( rs.next () )
        {
            list.add ( readField ( rs ) ) ;
        }

        // clear parameters
        ps.clearParameters () ;

        // return result
        return list ;
    }

    @Override
    public void updateResult ( final Identity taskid, TaskResult result ) throws SQLException
    {
        // get prepared statement
        final PreparedStatement ps = getPreparedStatement ( SQL_UPDATE_RESULT ) ;

        // set parameters
        ps.setLong ( 1, result.getTotal () ) ;
        ps.setLong ( 2, result.getPass () ) ;
        ps.setInt ( 3, taskid.getId () ) ;

        // execute
        ps.executeUpdate () ;

        // clear parameters
        ps.clearParameters () ;
    }

    @Override
    protected TestTask getTaskImpl ( final Identity taskid ) throws SQLException
    {
        // get prepared statement
        final PreparedStatement ps = getPreparedStatement ( SQL_GET_TASK ) ;

        // set parameters
        ps.setInt ( 1, taskid.getId () ) ;

        // execute
        final ResultSet rs = ps.executeQuery () ;

        // read task
        TestTask t = null ;
        if ( rs.next () )
        {
            t = readTask ( rs ) ;
        }

        // clear parameters
        ps.clearParameters () ;

        // return result
        return t ;
    }

    @Override
    protected List < TestTable > listTablesImpl ( final Identity taskid ) throws SQLException
    {
        // get prepared statement
        final PreparedStatement ps = getPreparedStatement ( SQL_LIST_TABLE ) ;

        // execute
        final ResultSet rs = ps.executeQuery () ;

        // read fields
        final List < TestTable > list = new LinkedList < TestTable > () ;
        while ( rs.next () )
        {
            list.add ( readTable ( rs ) ) ;
        }

        // clear parameters
        ps.clearParameters () ;

        // return result
        return list ;
    }

    @Override
    protected List < TestTask > listTasksImpl () throws SQLException
    {
        // get prepared statement
        final PreparedStatement ps = getPreparedStatement ( SQL_LIST_TASK ) ;

        // execute
        final ResultSet rs = ps.executeQuery () ;

        // read fields
        final List < TestTask > list = new LinkedList < TestTask > () ;
        while ( rs.next () )
        {
            list.add ( readTask ( rs ) ) ;
        }

        // clear parameters
        ps.clearParameters () ;

        // return result
        return list ;
    }

}
